﻿'By: CHUN SOKCHEA
'Student of Build Bright University
'Mail: chunsokchea@gmailc.om
'Phone: 087 397 317
Imports System.Data.SqlClient
Imports System.Reflection

Public Class myDataset
    Private Shared MyDataset As New DataSet
    Private Shared ConnectionString As String = My.Settings.ConString
    Private Shared cmd As New SqlCommand
    Private Shared dataAdapter As New SqlDataAdapter
    Private Shared tran As SqlTransaction = Nothing
    Private Shared cn As SqlConnection = Nothing
    'Private Shared ds As New SaleNStock

    Public Shared Function dataset() As DataSet
        Return MyDataset
    End Function
    Public Shared Function connection() As SqlConnection
        Try
            If cn Is Nothing Then
                cn = New SqlConnection(ConnectionString)
            End If
            If cn.State <> ConnectionState.Open Then
                cn.Open()
            End If
            Return cn
        Catch generatedExceptionName As SqlException
            cn = New SqlConnection(ConnectionString)
            cn.Open()
            Return cn
        End Try
    End Function
    Friend Shared Function getdata(ByVal text As String) As Boolean
        Try
            'ConnectionString = My.Settings.ConString
            'cn = New SqlConnection(ConnectionString)
            cmd = New SqlCommand("SELECT * FROM " & text, connection)
            dataAdapter = New SqlDataAdapter
            dataAdapter.SelectCommand = cmd
            dataAdapter.Fill(MyDataset, text.ToLower)
            connection.Close()
            Return True
        Catch ex As Exception
            myMsgbox.show("Can not connect to table : " & text & ", please wait and try again later!!!", "Getdata")
            Return False
        End Try
    End Function

    Friend Shared Function searchdata(ByVal data As DataGridView, ByVal dt As DataTable, ByVal text As String, ByVal tsearch As String, Optional ByVal tsort As String = "") As DataTable
        data.DataSource = Nothing
        Dim dr As New DataView(dt)
        Try
            dr.RowFilter = tsearch & " LIKE '%" + text + "%'"
            dr.Sort = tsort
            data.DataSource = dr
            Return dt
        Catch ex As Exception
            myMsgbox.show("Error Can not Search", "Search")
            Return Nothing
        End Try
    End Function

    ''Source from google
    Public Shared Function ToDataTable(Of T)(ByVal items As List(Of T)) As DataTable
        Dim tb = New DataTable(GetType(T).Name)
        Dim props As PropertyInfo() = GetType(T).GetProperties(BindingFlags.[Public] Or BindingFlags.Instance)
        For Each prop In props
            tb.Columns.Add(prop.Name, prop.PropertyType)
        Next
        For Each item In items
            Dim values = New Object(props.Length - 1) {}
            For i = 0 To props.Length - 1
                values(i) = props(i).GetValue(item, Nothing)
            Next
            tb.Rows.Add(values)
        Next
        Return tb
    End Function

    Friend Shared Function AddRecord(ByVal table As String, ByVal ParamArray args As Object()) As Boolean
        Dim r As DataRow
        Dim tbl As DataTable = MyDataset.Tables(table)
        'Dim col() As String = {col1, col2, col3, col4, col5, col6, col7, col8, col9, col10}
        Try
            r = tbl.NewRow
            For i = 0 To args.Length - 1
                r.Item(i) = args(i)
            Next
            tbl.Rows.Add(r)
            Dim objCommandBuilder As New SqlCommandBuilder(dataAdapter)
            dataAdapter.Update(MyDataset, table)
            ' myMsgbox.showSuccess("បញ្ចូលទិន្នន័យបានសម្រេច", "បន្ថែមថ្មី")
            Return True
        Catch ex As Exception
            Return False
            myMsgbox.show("បរាជ័យ!!! មិនអាចទាក់ទងServerបាន! សូមព្យាយាមលើកក្រោយ", "បន្ថែមថ្មី")
        End Try
    End Function

    Friend Shared Function UpdateRecord(ByVal table As String, ByVal id As Integer, ByVal ParamArray args As Object()) As Boolean
        Dim r As DataRow
        Dim tbl As DataTable = MyDataset.Tables(table)
        'Dim col() As String = {col1, col2, col3, col4, col5, col6, col7, col8, col9, col10}
        Try
            For j = 0 To tbl.Rows.Count - 1
                If tbl.Rows(j).Item(0) = id Then
                    r = tbl.Rows(j)
                    Exit For
                End If
            Next
            For i = 0 To args.Length - 1
                r.Item(i) = args(i)
            Next
            Dim objCommandBuilder As New SqlCommandBuilder(dataAdapter)
            dataAdapter.Update(MyDataset, table)
            myMsgbox.show("កែប្រែទិន្នន័យបានសម្រេច!!!", "កែប្រែ")
            cn.Close()
            Return True
        Catch ex As Exception
            Return False
            myMsgbox.show("បរាជ័យ!!! មិនអាចទាក់ទងserverបាន! ​សូមព្យាយាមលើកក្រោយ", "កែប្រែ")
        End Try
    End Function

    Friend Shared Function DeleteData(ByVal table As String, ByVal id As String) As Boolean
        Dim tbl As DataTable = MyDataset.Tables(table)
        Dim r As DataRow
        Try
            For j = 0 To tbl.Rows.Count - 1
                If tbl.Rows(j).Item(0) = id Then
                    r = tbl.Rows(j)
                    tbl.DefaultView.Delete(j)
                    Exit For
                End If
            Next
            Dim objCommandBuilder As New SqlCommandBuilder(dataAdapter)
            dataAdapter.Update(MyDataset, table)
            myMsgbox.show("លុបទិន្ននន័យបានសម្រេច!!!", "លុប")
            Return True
            cn.Close()
        Catch ex As Exception
            Return False
            myMsgbox.show("លុបបរាជ័យ!!! មិនអាចទាក់ទង Database Server បាន! សូមព្យាយាមលើកក្រោយ!", "លុប")
        End Try
    End Function

    Friend Shared Sub begin()
        If tran Is Nothing Then
            tran = connection().BeginTransaction()
            If cmd Is Nothing Then
                cmd = connection().CreateCommand()
            End If
            cmd.Transaction = tran
        End If
    End Sub

    Friend Shared Sub commit()
        tran.Commit()
        tran = Nothing
    End Sub

    Friend Shared Sub rollback()
        tran.Rollback()
        tran = Nothing
    End Sub

    Public Shared ReadOnly Property Trans() As SqlTransaction
        Get
            Return tran
        End Get
    End Property

End Class
